SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE sprTheoDoiChuongTrinhChietKhauMotNamReport
	@BranchID UNIQUEIDENTIFIER,
	@Month INT,
	@Year INT,
	@BrandID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @BrandCode NVARCHAR(20)
	SELECT @BrandCode = BrandCode FROM dbo.ThuongHieu WHERE BrandID = @BrandID
	
	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)
	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID
	
	DECLARE @Duration int
	SET @Duration = 12

	CREATE TABLE #tblMonthlyRevenue
	(
		ID INT IDENTITY(1, 1)
		,[Month] INT
		,[Year] INT
		,[DoanhSo] MONEY
		,KhachHangID UNIQUEIDENTIFIER
		,ChuongTrinhID UNIQUEIDENTIFIER
		,LoaiSanPham nvarchar(100)
	)

	CREATE TABLE #tblQuarterlyRevenue
	(
		ID INT IDENTITY(1, 1)
		,Quarter nvarchar(100)
		,QuarterIndex INT
		,[DoanhSo] MONEY
		,KhachHangID UNIQUEIDENTIFIER
		,ChuongTrinhID UNIQUEIDENTIFIER
		,LoaiSanPham nvarchar(100)
	)

		INSERT INTO #tblMonthlyRevenue(KhachHangID, [Month], [Year], [DoanhSo], [LoaiSanPham], [ChuongTrinhID])
		SELECT mr.KhachHangID
		, [Thang], [Nam]
		, DoanhSoTD as DoanhSo
		, LoaiSanPham
		, kh.ID
		FROM vwGetTotalMonthlyRevenue mr
		inner join KhachHangDuThuong kh ON mr.KhachHangID = kh.HoiVienID
		AND kh.Duration=@Duration
		INNER JOIN ThuongHieu th ON th.BrandID = kh.BrandID
		WHERE CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
		AND CAST((cast(@Year as nvarchar(4)) + '-' + CAST(@Month as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME) BETWEEN StartDate AND EndDate
		AND @BrandID = kh.BrandID

		DECLARE @ChuongTrinhID UNIQUEIDENTIFIER
		DECLARE DoanhSoCursor CURSOR FOR SELECT ChuongTrinhID FROM #tblMonthlyRevenue
		Open DoanhSoCursor

		FETCH NEXT FROM DoanhSoCursor
		INTO @ChuongTrinhID

		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @StartDate DATETIME
			DECLARE @EndDate DateTime
			DECLARE @KhachHangID UNIQUEIDENTIFIER
			DECLARE @LoaiSanPham nvarchar(100)

			SELECT @StartDate = StartDate, @KhachHangID = HoiVienID, @LoaiSanPham = LoaiSanPham
			FROM KhachHangDuThuong WHERE ID = @ChuongTrinhID

			SET @EndDate = @StartDate
			SET @EndDate = DATEADD(month,12,  @EndDate)

			DECLARE @Quarter INT
			SET @Quarter = 1

			WHILE @StartDate < @EndDate
			BEGIN
				DECLARE @tempEndDate DATETIME
				SET @tempEndDate = DATEADD(month, 3, @StartDate)

				INSERT INTO #tblQuarterlyRevenue(KhachHangID, [Quarter], [DoanhSo], [LoaiSanPham], [ChuongTrinhID], QuarterIndex)
				SELECT @KhachHangID, RTRIM(@LoaiSanPham) + CAST(@Quarter as nvarchar(1)), ISNULL(SUM(DoanhSo), 0), @LoaiSanPham, @ChuongTrinhID, @Quarter
				FROM #tblMonthlyRevenue mr
				WHERE mr.ChuongTrinhID = @ChuongTrinhID
				AND @startDate <= CAST((cast([Year] as nvarchar(4)) + '-' + CAST([Month] as nvarchar(2)) +'-' + CAST(DATEPART(day, @StartDate) as nvarchar(2))) as DATETIME)
				AND @tempEndDate > CAST((cast([Year] as nvarchar(4)) + '-' + CAST([Month] as nvarchar(2)) +'-' + CAST(DATEPART(day, @StartDate) as nvarchar(2))) as DATETIME)
				
				SET @StartDate = @tempEndDate
				
				SET @Quarter = @Quarter + 1
			END
			FETCH NEXT FROM DoanhSoCursor
			INTO @ChuongTrinhID
		END


		SELECT *
--		, @TrungTamName as TrungTamName
--		, ds.DoanhSo as DoanhSoCamKet
--		, ds.DoanhSoLapQuy 
		FROM (
		SELECT KhachHangID,[AccountingObjectCode]
		,[AccountingObjectName], qr.ChuongTrinhID, qr.LoaiSanPham, qr.DoanhSo, qr.Quarter 
		, @TrungTamName as TrungTamName
		, ds.DoanhSo as DoanhSoCamKet
		, ds.DoanhSoLapQuy
		, th.BrandName
		, MucChietKhauTD
		, MucChietKhauCC
		from #tblQuarterlyRevenue qr
		LEFT JOIN KhachHangDuThuong kh ON kh.ID = qr.ChuongTrinhID
		LEFT JOIN ThuongHieu th		ON th.BrandID = kh.BrandID
		LEFT JOIN DoanhSoCamKet ds ON ds.KhachHangDuThuongID = qr.KhachHangID  
		AND ds.Quarter = qr.QuarterIndex
		LEFT JOIN [ATAHANOI].[dbo].[AccountingObject] ao ON qr.KhachHangID = ao.AccountingObjectID
		
		) AS TDTable 
		pivot (SUM (DoanhSo) FOR Quarter IN ([TD1], [TD2], [TD3], [TD4], [CC1], [CC2], [CC3], [CC4])) as pvt
--		INNER JOIN DoanhSoCamKet ds ON ds.KhachHangDuThuongID = pvt.KhachHangID-- AND ds.[Month] = DATEDIFF(month, StartDate, CAST((cast(Nam as nvarchar(4)) + '-' + CAST(Thang as nvarchar(2)) +'-' + CAST(DATEPART(day, StartDate) as nvarchar(2))) as DATETIME))

	CLOSE DoanhSoCursor;
	DEALLOCATE DoanhSoCursor;
	DROP TABLE #tblQuarterlyRevenue
	DROP TABLE #tblMonthlyRevenue

	--exec sprTheoDoiChuongTrinhChietKhauMotNamReport '5CF2DF83-3799-43FE-AAF5-04968C57F029', 1, 2010, 1
END
GO

--exec sprTheoDoiChuongTrinhChietKhauMotNamReport '5CF2DF83-3799-43FE-AAF5-04968C57F029', 1, 2010, 1
